using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
namespace SqlCeDriver
{
	class SqlServerScripter
	{
		/*
		 *  Build a script for a given table from metadata.
		 * The metadata table contains the follwing columns.
		 * 0 = Field name
		 * 1 = Type
		 * 2 = length
		 * 3 = Nulls allowed flag
		 * 4 = Calculated field flag (nb: calculated fields are not supported)
		 * 5 = Tags for additional info eg PK, AUTO etc.
		 * 6 = Index or other constraint names
		 * 7 = Other information eg details of default values (nb: default values not supported)
		 * 
		 * Not done:
		 * - defaults
		 * - not for Replication
		 * - foreign keys
		 * - indexes
		 *   1. Check Extra and Extra detail.
		 *   2. Where nth , item is IDXn index name is nth Extra detail.
		 *   3. create hash of arrays on names - see array stuff for adding elements.
		 */

		String sourceTableName;
		SqlTableSchema schema;
		/*
				public static String MakeCopyScript(String strConnectionString, String SourceTableName, String TargetTableName)
				{
					SqlServerScripter engine = new SqlServerScripter(strConnectionString, SourceTableName, TargetTableName);
					return engine.Script();
				}
		*/

		public SqlServerScripter(String tableName, SqlTableSchema schema)
		{
			this.sourceTableName = tableName;
			this.schema = schema;
		}

		public String ScriptCreateTable(bool forStaging)
		{
			String script = "";

			ArrayList scriptRows = new ArrayList();  // holds target sql 
			// bit odd: inherited from SqlServer drive that has schema.table
			String[] tableParts = new String[] { this.sourceTableName };
			// now build the script. 
			scriptRows.Add(String.Format("create table {0} (", Helper.SafeTableName(tableParts[0])));
			bool isPkey = false;      // flag to detect a Primary key field
			String strConn = ",";      // for comma logic
			// loop through each fiels
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				isPkey = (isPkey | fi.IsPKey); // capture Pkey
				scriptRows.Add(this.FieldSqlFromFieldInfo(fi, forStaging) + strConn);
			}
			// add the PKey definition if required
			if (isPkey)
				this.AddPrimaryKey(scriptRows, tableParts[0]);

			this.StripFinalComma(scriptRows); // may or may not be necessary
			scriptRows.Add(")");

			script = String.Join("\r\n", (String[])scriptRows.ToArray(typeof(String)));

			return script;
		}

		public String ScriptCreateTable()
		{
			return this.ScriptCreateTable(true);
		}

		private void StripFinalComma(ArrayList scriptRows)
		{
			int lastLineIdx = scriptRows.Count - 1;
			if (lastLineIdx < 0) return;   // nope. Array is empty
			String lastLine = scriptRows[lastLineIdx].ToString();
			if (lastLine.Length == 0) return; // nope. last line is blank
			if (lastLine[lastLine.Length - 1] == ',') // last line terminates with , (assume no spare spaces on the end)
			{
				lastLine = lastLine.Substring(0, lastLine.Length - 1);
				scriptRows[lastLineIdx] = lastLine;
			}
		}

		// todo identity
		// data with ' in
		// options for []
		// use string builder
		public String ScriptData(DataTable table)
		{
			// define 2 sanity constraints
			// abandon if the script runs above this
			const int MAX_SCRIPT_LENGTH = 5000000;
			// copy to clipboard if above this size
			const int MAX_DISPLAY_LENGTH = 500000;

			StringBuilder script = new StringBuilder("");
			int scriptLength = 0; // sanity 
			script.Append("delete from " + table.TableName + ";\r\n");
			//
			int seed = 0;
			int inc = 1;
			// test for auto-increment field
			DbView.FieldInfo idField = this.schema.IdentityField;
			if (idField != null)
			{
				inc = idField.AutoInc;
				// identidy insert
				script.Append(String.Format("set IDENTITY_INSERT [{0}] ON; \r\n", table.TableName));
			}
			foreach (DataRow dr in table.Rows)
			{
				String scriptRow = String.Format("insert into {0} (%1) values (%2);\r\n", table.TableName);
				String fields = "";
				String values = "";
				for (int idx = 0; idx < dr.ItemArray.GetLength(0); ++idx)
				{
					String field = table.Columns[idx].ColumnName;
					DbView.FieldInfo fi = this.schema[field];
					if (!fi.IsCalc) // not if calculated
					{
						if (fields.Length > 0)
							fields += ", ";
						fields += MakeInsertFieldName(table.Columns[idx].ColumnName);
						if (values.Length > 0)
							values += ", ";
						String data = dr[idx].ToString();
						if (data.Length == 0)
							values += "NULL";
						else
						{
							if (fi.Type == "D")
								values += "'" + MakeDateInsertField(dr[idx]) + "'";
							else if (fi.Type == "B")
								values += MakeBitInsertField(dr[idx]);
							else if (fi.Type == "C")
							{
								String prefix = (fi.IsUnicode) ? "N" : "";
								values += prefix + "'" + dr[idx].ToString().Replace("\'", "\'\'") + "'";
							}
							else
								values += dr[idx].ToString();
							if (fi == idField)
							{
								int ival;
								if (int.TryParse(dr[idx].ToString(), out ival))
								{
									if (ival > seed) seed = ival; // update
								}
							}
						}
					}
				}
				String recordScript = scriptRow.Replace("%1", fields).Replace("%2", values);
				script.Append(recordScript);
				scriptLength += recordScript.Length;
				if (scriptLength > MAX_SCRIPT_LENGTH)
					throw new Exception("Data set too large to script (Sorry!)");
			}
			if (idField != null)
			{
				// identidy insert
				script.Append(String.Format(@"
set IDENTITY_INSERT [{0}] OFF;
ALTER TABLE {0} ALTER COLUMN {1} IDENTITY ({2},{3});
", table.TableName, idField.Name, seed + inc, inc)); //seed plus inc to set to next free.
			}

			if (scriptLength > MAX_DISPLAY_LENGTH)
			{
				System.Windows.Forms.Clipboard.SetText(script.ToString());
				return "*** The script is too large to display. It's been dumped into the clipboard instead ***";
			}

			return script.ToString();
		}

		public String ScriptContentCopy(String sourceTableName, String targetTableName, SqlScriptingHelper scriptHelper)
		{
			if (scriptHelper.Schema.PKeyFields.GetLength(0) > 0)
				return this.ScriptContentCopyPkey(sourceTableName, targetTableName, scriptHelper);
			else
				return this.ScriptContentCopyNoPkey(sourceTableName, targetTableName, scriptHelper);
		}

		public String ScriptContentCopyPkey(String sourceTableName, String targetTableName, SqlScriptingHelper scriptHelper)
		{
			int max = 1; // wrong. Needs to be the next Id number
			String script = "/*  Script to copy content */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/

			script += String.Format(@"
delete from [{0}] ;", targetTableName);

			DbView.FieldInfo idField = scriptHelper.Schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
                set IDENTITY_INSERT [{0}] ON; 
                ", targetTableName);
			}
			/*
insert into  BANK (list)
select list
 from BANK2
where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
insert into [{0}] ({2})
select {2} 
  from [{1}] ;
            ", targetTableName, sourceTableName, scriptHelper.ScriptAllButCalcFields());

			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
set IDENTITY_INSERT [{0}] OFF; 
                ", targetTableName);

				script += String.Format(@"
alter table [{0}] ALTER COLUMN {1} IDENTITY ({2},1); ", targetTableName, idField.Name, max); //
			}
			return script;
		}

		public String ScriptContentCopyNoPkey(String sourceTableName, String targetTableName, SqlScriptingHelper scriptHelper)
		{
			String script = "/*  Script to copy content */\n";
			script += String.Format("delete from [{0}];\n", targetTableName);
			DbView.FieldInfo idField = scriptHelper.Schema.IdentityField;
			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
                set IDENTITY_INSERT [{0}] ON;
                ", targetTableName);
			}
			/*
insert into  BANK (list)
select list
 from BANK2
where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
insert into [{0}] ({2})
select {2} 
  from [{1}]
", targetTableName, sourceTableName, scriptHelper.ScriptAllButCalcFields());

			if (idField != null)
			{
				// identidy insert
				script += String.Format(@"
set IDENTITY_INSERT [{0}] OFF 
                ", targetTableName);

				script += String.Format(@"
declare @reseed int
select @reseed = max({2}) from [{1}]
DBCC CHECKIDENT ([{0}], RESEED, @reseed)
                ", targetTableName, targetTableName, idField.Name);
			}
			return script;
		}

		// when creating a copy of one table from another you need to create new
		// default constraint names. This routine first tries to replace  
		// any substrings of the old table name with the new table name.
		// if this fails ie. the old table name is not in the constraint name 
		// then as a fallback it suffixes the new table name to the end of the
		// constraint name.
		String FixText(String target, String sourceTable, String targetTable)
		{
			// regex to find the source table name in 
			String newName = target.Replace(sourceTable, targetTable);
			if (newName == target)
				newName = String.Format("{0}_{1}", newName, targetTable);
			return newName;
		}

		void AddPrimaryKey(ArrayList scriptRows, String tableName)
		{
			int pkeyCount = 0;
			ArrayList keyFieldList = new ArrayList();
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					keyFieldList.Add(fi.SafeName);
					++pkeyCount;
				}
			}
			// the boilerplate is hacked from table scripts from other sources not from the 
			// table itself.
			if (pkeyCount > 0)
			{
				scriptRows.Add(String.Format("CONSTRAINT [PK_{0}] PRIMARY KEY (", tableName));
				scriptRows.Add(String.Join(",\r\n", (String[])keyFieldList.ToArray(typeof(String))));
				scriptRows.Add(")");
			}
		}

		/*
		 *  Detect and script defaults incrementing fields.
		 */
		String GetDefaultInfo(DbView.FieldInfo fi)
		{
			String defaulltInfo = "";
			if (fi.HasDefault)
			{
				String defaultName = String.Format("DF_{0}_{1}", this.sourceTableName, fi.Name);
				defaulltInfo = String.Format(" CONSTRAINT {0} DEFAULT {1}", defaultName, fi.ConstraintDefinition);
			}
			return defaulltInfo;
		}

		String GetComputedInfo(DbView.FieldInfo fi)
		{
			String info = "";
			if (fi.IsCalc)
			{
				info = fi.ConstraintDefinition;
			}
			return info;
		}

		String GetIdentityInfo(DbView.FieldInfo fi)
		{
			String identInfo = "";
			if (fi.IsAuto)
			{
				identInfo = " IDENTITY";
				identInfo += String.Format("({0},{1})", fi.AutoSeed, fi.AutoInc);
			}
			return identInfo;
		}

		/*
		 *  Script a field
		 */
		//String FieldSqlFromFieldInfo(DbView.FieldInfo fi, bool forStaging)
		//{
		//    String sql = "";
		//    // todo: bool fi.NonNull property
		//    String NullStr = ""; // allow nulls Yes/No
		//    String NonNull = "";
		//    if (NullStr.Length > 0)
		//        if (NullStr[0] == 'N' || NullStr[0] == 'n')
		//            NonNull = " NOT NULL ";
		//    String fieldType = fi.SqlType;
		//    bool lenReqd = (fieldType.ToUpper().IndexOf("CHAR") != -1);
		//    if (lenReqd)
		//    {
		//        String FieldSize = (fi.Length > 0) ? fi.Length.ToString() :
		//                            (fi.Length == -1) ? "MAX" : fi.Length.ToString();
		//        fieldType = String.Format("{0}({1})", fieldType, FieldSize);
		//    }
		//    String IdentityInfo = GetIdentityInfo(fi);
		//    String DefaultInfo = GetDefaultInfo(fi);
		//    // If the table is for staging (specialised) then don't enforce the computed, non-null or default
		//    // (that will be done in the update)
		//    if (forStaging)
		//    {
		//        NonNull = "";
		//        DefaultInfo = "";
		//    }
		//    if (fi.IsCalc && !forStaging)
		//        sql = String.Format("[{0}] AS {1}", fi.Name, GetComputedInfo(fi));
		//    else
		//        sql = String.Format("[{0}] {1}{2}{3}{4}", fi.Name, fieldType, IdentityInfo, NonNull, DefaultInfo);

		//    return sql;

		////}

		String FieldSqlFromFieldInfo(DbView.FieldInfo fi, bool forStaging)
		{
			String sql = "";
			String nonNull = (fi.AllowsNulls) ? "" : " NOT NULL ";
			String fieldType = fi.SqlType;
			// adjust to add length qualifiers. Hm. Floats not done yet
			if (fieldType.ToUpper().IndexOf("CHAR") != -1)
			{
				String fieldSize = (fi.Length > 0) ? fi.Length.ToString() :
									(fi.Length == -1) ? "MAX" : fi.Length.ToString();
				fieldType = String.Format("{0}({1})", fieldType, fieldSize);
			}
			else if (RequiresDp(fi))
			{
				fieldType = String.Format("{0}({1},{2})", fieldType, fi.Length, fi.Dp);
			}

			String identityInfo = this.GetIdentityInfo(fi);
			String defaultInfo = this.GetDefaultInfo(fi);
			// If the table is for staging (specialised) then don't enforce the computed, non-null or default
			// (that will be done in the update)
			if (forStaging)
			{
				nonNull = "";
				defaultInfo = "";
			}
			if (fi.IsCalc && !forStaging)
				sql = String.Format("[{0}] AS {1}", fi.Name, this.GetComputedInfo(fi));
			else
				sql = String.Format("[{0}] {1}{2}{3}{4}", fi.Name, fieldType, identityInfo, nonNull, defaultInfo);

			return sql;
		}
		String FieldSqlFromFieldInfo(DbView.FieldInfo fi)
		{
			return this.FieldSqlFromFieldInfo(fi, false);
		}

		static bool RequiresDp(DbView.FieldInfo fi)
		{
			return (fi.SqlType.ToUpper() == "DECIMAL" ||
					fi.SqlType.ToUpper() == "NUMERIC") ? true : false;
		}

		static String MakeInsertFieldName(String raw)
		{
			return String.Format("[{0}]", raw);
		}

		static String MakeDateInsertField(object o)
		{
			// assume 
			DateTime dt = (DateTime)o;
			String s = dt.ToString("yyyyMMdd HH:mm:ss");
			return s;
		}

		static String MakeBitInsertField(object o)
		{
			// assume 
			bool b = (bool)o;
			return (b) ? "1" : "0";
		}

		static public long CountTableRows(SqlCeDataSource source, String tableSource)
		{
			String sql = String.Format("SELECT count(*) FROM {0}", tableSource);
			DataTable dt = new Helper(source.GetAccessor()).GenericQuery(source.ConnectionString, sql, "RowCount");
			long countStart = System.Convert.ToInt32(dt.Rows[0][0]);
			return countStart;
		}

		public void CopyTable(SqlCeDataSource source, String connTarget, String tableSource, String tableTarget, bool copyContent)
		{
			// before release add a check that the table does not exist 
			// may have different name, but has same schema
			SqlServerScripter newTableBuilder = new SqlServerScripter(tableTarget, this.schema);
			String script = newTableBuilder.ScriptCreateTable();
			try
			{
				bool success;
				new Helper(source.GetAccessor()).GenericExecute(connTarget, script, out success);
				if (copyContent == false) return; // job done.
			}
			catch (Exception e)
			{
				String extraMessage = (tableSource.IndexOf(".") > 0) ? "\nor the schema does not exist in the target database" : "";
				String message = String.Format("Error: Probably either: Target table exists or " +
											   "Target database is readonly{1}.\nDetail:\n{0}", e.Message, extraMessage);
				throw new Exception(message);
			}

			//try
			//{
			//    SqlConnection cnn = new SqlConnection(connSource);
			//    // Getting source data
			//    SqlCommand cmd = new SqlCommand(String.Format("SELECT * FROM {0}", tableSource), cnn);
			//    cnn.Open();
			//    SqlDataReader rdr = cmd.ExecuteReader();
			//    // Initializing an SqlBulkCopy object
			//    SqlBulkCopy sbc = new SqlBulkCopy(connTarget, SqlBulkCopyOptions.KeepIdentity);
			//    // Copying data to destination
			//    sbc.DestinationTableName = tableTarget;
			//    sbc.BatchSize = 1000;    // 1000 rows at a time
			//    sbc.NotifyAfter = 1000;  // generate an event
			//    if (progress != null)
			//        sbc.SqlRowsCopied += progress;
			//    sbc.WriteToServer(rdr);
			//    // Closing connection and the others
			//    sbc.Close();
			//    rdr.Close();
			//    cnn.Close();
			//}
			//catch (SqlException e)
			//{
			//    // may not work if connection fails
			//    String Message = String.Format("Table Copy Error:\n {0}", e.Message);
			//    throw new Exception(Message);
			//    // MessageBox.Show(Message, "Copy Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			//}
		}
	}

	// static methods 
	internal class FieldScripter
	{
		internal static String ScriptField(DbView.FieldInfo fi, string tableName)
		{
			String sql = "";
			String nonNull = (fi.AllowsNulls) ? "" : " NOT NULL ";
			String fieldType = fi.SqlType;
			bool lenReqd = (fieldType.ToUpper().IndexOf("CHAR") != -1);
			if (lenReqd)
			{
				String fieldSize = (fi.Length > 0) ? fi.Length.ToString() :
									(fi.Length == -1) ? "MAX" : fi.Length.ToString();
				fieldType = String.Format("{0}({1})", fieldType, fieldSize);
			}
			else if (RequiresDp(fi))
			{
				fieldType = String.Format("{0}({1},{2})", fieldType, fi.Length, fi.Dp);
			}

			String identityInfo = GetIdentityInfo(fi);
			String defaultInfo = GetDefaultInfo(fi, tableName);
			if (fi.IsCalc)
				sql = String.Format("[{0}] AS {1}", fi.Name, GetComputedInfo(fi));
			else
				sql = String.Format("[{0}] {1}{2}{3}{4}", fi.Name, fieldType, identityInfo, nonNull, defaultInfo);

			return sql;
		}

		static bool RequiresDp(DbView.FieldInfo fi)
		{
			return (fi.SqlType.ToUpper() == "DECIMAL" ||
					fi.SqlType.ToUpper() == "NUMERIC") ? true : false;
		}

		static String GetDefaultInfo(DbView.FieldInfo fi, string tableName)
		{
			String defaultInfo = "";
			if (fi.HasDefault)
			{
				String defaultName = MakeDefaultName(fi, tableName);
				defaultInfo = String.Format(" CONSTRAINT {0} DEFAULT {1}", defaultName, fi.ConstraintDefinition);
			}
			return defaultInfo;
		}

		public static String MakeDefaultName(DbView.FieldInfo fi, string tableName)
		{
			return (fi.HasDefault) ? String.Format("DF_{0}_{1}", tableName.Replace(".", "_"), fi.Name) : "";
		}

		static String GetComputedInfo(DbView.FieldInfo fi)
		{
			String info = "";
			if (fi.IsCalc)
			{
				info = fi.ConstraintDefinition;
			}
			return info;
		}

		static String GetIdentityInfo(DbView.FieldInfo fi)
		{
			String identInfo = "";
			if (fi.IsAuto)
			{
				identInfo = " IDENTITY";
				identInfo += String.Format("({0},{1})", fi.AutoSeed, fi.AutoInc);
			}
			return identInfo;
		}
	}

	internal class FkScripter
	{
		public DataTable KeyInfoTable { get; set; }
		public int TableCol { get; set; }
		public int NameCol { get; set; }
		public int FieldCol { get; set; }
		public int PkTableCol { get; set; }
		public int PkCol { get; set; }
		public int DisabledCol { get; set; }
		public int ReplicationCol { get; set; }

		public void ConfigureTable(DataTable dt, int tableCol, int nameCol, int fieldCol, int pkeyTableCol, int pkeyCol)
		{
			this.KeyInfoTable = dt;
			this.TableCol = tableCol;
			this.NameCol = nameCol;
			this.FieldCol = fieldCol;
			this.PkTableCol = pkeyTableCol;
			this.PkCol = pkeyCol;
			this.DisabledCol = -1;
			this.ReplicationCol = -1;
		}

		public String ScriptKeys(String tgtTableName, bool allTables, bool dropOnly)
		{
			String script = ""; ;
			String prevTableName = "";
			for (int idx = 0; idx < this.KeyInfoTable.Rows.Count; ++idx)
			{
				DataRow dr = this.KeyInfoTable.Rows[idx];
				String fkeyName = dr[this.NameCol].ToString();
				String tableName = dr[this.TableCol].ToString();
				String pkeyTable = dr[this.PkTableCol].ToString();
				String fkeyField = this.GetFkFields(dr, fkeyName, idx);
				String pkeyField = this.GetPkFields(dr, fkeyName, idx);
				if (tableName == tgtTableName || allTables == true)
				{
					if (tableName != prevTableName)
					{
						script += "\n-- *** Scripting " + tableName + " ***";
						prevTableName = tableName;
					}
					script += String.Format("\n-- drop constraint if it exists already" +
										   "\nif object_id('{0}') is not null alter table {1} drop constraint {0}", fkeyName, tableName);
					String comment = "-- ";      // to discable
					if (!dropOnly) comment = ""; // only of drop only mode
					script += String.Format("\n-- Add without enforcing the constraint" +
											"\n{5}Alter table {0} with nocheck add constraint {1} foreign key({2}) references {3}({4})",
											tableName, fkeyName, fkeyField, pkeyTable, pkeyField, comment);
					// add possibly commented out not for replication clause
					comment = "-- "; // to discable
					if (this.IsNotForReplication(dr)) comment = ""; // enable it.
					script += String.Format(" {2}not for replication", tableName, fkeyName, comment);

					comment = "--";
					if (this.IsDisabled(dr) && !dropOnly) comment = ""; // enable it.
					script += String.Format("\n{2}Alter table {0} nocheck constraint {1}\n", tableName, fkeyName, comment);
				}
				idx += CalculateSkipRows(fkeyField);
			}
			return script;
		}

		private static int CalculateSkipRows(String fkeyField)
		{
			// skip rows for multi-part keys
			int idx = 0;
			int cidx = fkeyField.IndexOf(',');
			while (cidx != -1)
			{
				++idx;
				cidx = fkeyField.IndexOf(',', cidx + 1);
			}
			return idx;
		}

		private string GetFkFields(DataRow dr, String fkeyName, int idx)
		{
			String fields = dr[this.FieldCol].ToString();
			while (true)
			{
				++idx;
				if (idx >= this.KeyInfoTable.Rows.Count) return fields;
				if (this.KeyInfoTable.Rows[idx][this.NameCol].ToString() != fkeyName) return fields;
				fields += "," + this.KeyInfoTable.Rows[idx][this.FieldCol].ToString();
			}
		}

		private string GetPkFields(DataRow dr, String fkeyName, int idx)
		{
			String fields = dr[this.PkCol].ToString();
			while (true)
			{
				++idx;
				if (idx >= this.KeyInfoTable.Rows.Count) return fields;
				if (this.KeyInfoTable.Rows[idx][this.NameCol].ToString() != fkeyName) return fields;
				fields += "," + this.KeyInfoTable.Rows[idx][this.PkCol].ToString();
			}
		}

		bool IsDisabled(DataRow dr)
		{
			if (this.DisabledCol == -1) return false; // not configured. Assume na.
			String data = dr[this.DisabledCol].ToString();
			if (data.ToUpper() == "Y" || data == "1") // two possibilities
				return true;
			return false;
		}

		bool IsNotForReplication(DataRow dr)
		{
			if (this.ReplicationCol == -1) return false; // not configured. Assume na.
			String data = dr[this.ReplicationCol].ToString();
			if (data.ToUpper() == "Y" || data == "1") // two possibilities
				return true;
			return false;
		}
	}
}
